<!DOCTYPE html>
<html lang="zh-CN">
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <meta name="robots" content="noodp" />
        <meta http-equiv="X-UA-Compatible" content="IE=edge, chrome=1">
        <title>TIDB使用 - 德国粗茶淡饭</title><meta name="Description" content="TIDB使用"><meta property="og:title" content="TIDB使用" />
<meta property="og:description" content="TIDB使用" />
<meta property="og:type" content="article" />
<meta property="og:url" content="https://www.ctq6.cn/technology/ddbs/tidb%E4%BD%BF%E7%94%A8/" />
<meta property="og:image" content="https://www.ctq6.cn/logo.png"/>
<meta property="article:published_time" content="2021-02-16T20:07:07+08:00" />
<meta property="article:modified_time" content="2021-02-16T20:07:07+08:00" />
<meta name="twitter:card" content="summary_large_image"/>
<meta name="twitter:image" content="https://www.ctq6.cn/logo.png"/>

<meta name="twitter:title" content="TIDB使用"/>
<meta name="twitter:description" content="TIDB使用"/>
<meta name="application-name" content="LoveIt">
<meta name="apple-mobile-web-app-title" content="LoveIt"><meta name="theme-color" content="#ffffff"><meta name="msapplication-TileColor" content="#da532c"><link rel="shortcut icon" type="image/x-icon" href="/favicon.ico" />
        <link rel="icon" type="image/png" sizes="32x32" href="/favicon-32x32.png">
        <link rel="icon" type="image/png" sizes="16x16" href="/favicon-16x16.png"><link rel="apple-touch-icon" sizes="180x180" href="/apple-touch-icon.png"><link rel="mask-icon" href="/safari-pinned-tab.svg" color="#5bbad5"><link rel="manifest" href="/site.webmanifest"><link rel="canonical" href="https://www.ctq6.cn/technology/ddbs/tidb%E4%BD%BF%E7%94%A8/" /><link rel="prev" href="https://www.ctq6.cn/technology/logging/efk%E6%97%A5%E5%BF%97%E5%B9%B3%E5%8F%B0%E9%83%A8%E7%BD%B2/" /><link rel="next" href="https://www.ctq6.cn/technology/cicd/jenkins%E5%AE%89%E8%A3%85/" /><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/normalize.css@8.0.1/normalize.min.css"><link rel="stylesheet" href="/css/style.min.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free@5.13.0/css/all.min.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/animate.css@3.7.2/animate.min.css"><script type="application/ld+json">
    {
        "@context": "http://schema.org",
        "@type": "BlogPosting",
        "headline": "TIDB使用",
        "inLanguage": "zh-CN",
        "mainEntityOfPage": {
            "@type": "WebPage",
            "@id": "https:\/\/www.ctq6.cn\/technology\/ddbs\/tidb%E4%BD%BF%E7%94%A8\/"
        },"image": [{
                            "@type": "ImageObject",
                            "url": "https:\/\/www.ctq6.cn\/images\/Apple-Devices-Preview.png",
                            "width":  3200 ,
                            "height":  2048 
                        }],"genre": "technology","keywords": "tidb","wordcount":  4653 ,
        "url": "https:\/\/www.ctq6.cn\/technology\/ddbs\/tidb%E4%BD%BF%E7%94%A8\/","datePublished": "2021-02-16T20:07:07+08:00","dateModified": "2021-02-16T20:07:07+08:00","license": "This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License.","publisher": {
            "@type": "Organization",
            "name": "xxxx","logo": {
                    "@type": "ImageObject",
                    "url": "https:\/\/www.ctq6.cn\/images\/avatar.png",
                    "width":  528 ,
                    "height":  560 
                }},"author": {
                "@type": "Person",
                "name": "MikelPan"
            },"description": "TIDB使用"
    }
    </script></head>
    <body header-desktop="fixed" header-mobile="auto"><script type="text/javascript">(window.localStorage && localStorage.getItem('theme') ? localStorage.getItem('theme') === 'dark' : ('auto' === 'auto' ? window.matchMedia('(prefers-color-scheme: dark)').matches : 'auto' === 'dark')) && document.body.setAttribute('theme', 'dark');</script>

        <div id="mask"></div><div class="wrapper"><header class="desktop" id="header-desktop">
    <div class="header-wrapper">
        <div class="header-title">
            <a href="/" title="德国粗茶淡饭"><span class="header-title-pre"><i class='far fa-kiss-wink-heart fa-fw'></i></span>德国粗茶淡饭</a>
        </div>
        <div class="menu">
            <div class="menu-inner"><a class="menu-item" href="/posts/"> 生活 </a><a class="menu-item" href="/technology/"> 技术 </a><a class="menu-item" href="/tags/"> 标签 </a><a class="menu-item" href="/categories/"> 分类 </a><a class="menu-item" href="/categories/documentation/"> 文档 </a><a class="menu-item" href="/about/"> 关于 </a><a class="menu-item" href="https://github.com/MikelPan/Cnblog.git" title="GitHub" rel="noopener noreffer" target="_blank"><i class='fab fa-github fa-fw'></i>  </a><span class="menu-item delimiter"></span><a href="javascript:void(0);" class="menu-item language" title="选择语言">简体中文<i class="fas fa-chevron-right fa-fw"></i>
                        <select class="language-select" id="language-select-desktop" onchange="location = this.value;"><option value="/technology/ddbs/tidb%E4%BD%BF%E7%94%A8/" selected>简体中文</option></select>
                    </a><span class="menu-item search" id="search-desktop">
                        <input type="text" placeholder="搜索文章标题或内容..." id="search-input-desktop">
                        <a href="javascript:void(0);" class="search-button search-toggle" id="search-toggle-desktop" title="搜索">
                            <i class="fas fa-search fa-fw"></i>
                        </a>
                        <a href="javascript:void(0);" class="search-button search-clear" id="search-clear-desktop" title="清空">
                            <i class="fas fa-times-circle fa-fw"></i>
                        </a>
                        <span class="search-button search-loading" id="search-loading-desktop">
                            <i class="fas fa-spinner fa-fw fa-spin"></i>
                        </span>
                    </span><a href="javascript:void(0);" class="menu-item theme-switch" title="切换主题">
                    <i class="fas fa-adjust fa-fw"></i>
                </a>
            </div>
        </div>
    </div>
</header><header class="mobile" id="header-mobile">
    <div class="header-container">
        <div class="header-wrapper">
            <div class="header-title">
                <a href="/" title="德国粗茶淡饭"><span class="header-title-pre"><i class='far fa-kiss-wink-heart fa-fw'></i></span>德国粗茶淡饭</a>
            </div>
            <div class="menu-toggle" id="menu-toggle-mobile">
                <span></span><span></span><span></span>
            </div>
        </div>
        <div class="menu" id="menu-mobile"><div class="search-wrapper">
                    <div class="search mobile" id="search-mobile">
                        <input type="text" placeholder="搜索文章标题或内容..." id="search-input-mobile">
                        <a href="javascript:void(0);" class="search-button search-toggle" id="search-toggle-mobile" title="搜索">
                            <i class="fas fa-search fa-fw"></i>
                        </a>
                        <a href="javascript:void(0);" class="search-button search-clear" id="search-clear-mobile" title="清空">
                            <i class="fas fa-times-circle fa-fw"></i>
                        </a>
                        <span class="search-button search-loading" id="search-loading-mobile">
                            <i class="fas fa-spinner fa-fw fa-spin"></i>
                        </span>
                    </div>
                    <a href="javascript:void(0);" class="search-cancel" id="search-cancel-mobile">
                        取消
                    </a>
                </div><a class="menu-item" href="/posts/" title="">生活</a><a class="menu-item" href="/technology/" title="">技术</a><a class="menu-item" href="/tags/" title="">标签</a><a class="menu-item" href="/categories/" title="">分类</a><a class="menu-item" href="/categories/documentation/" title="">文档</a><a class="menu-item" href="/about/" title="">关于</a><a class="menu-item" href="https://github.com/MikelPan/Cnblog.git" title="GitHub" rel="noopener noreffer" target="_blank"><i class='fab fa-github fa-fw'></i></a><a href="javascript:void(0);" class="menu-item theme-switch" title="切换主题">
                <i class="fas fa-adjust fa-fw"></i>
            </a><a href="javascript:void(0);" class="menu-item" title="选择语言">简体中文<i class="fas fa-chevron-right fa-fw"></i>
                    <select class="language-select" onchange="location = this.value;"><option value="/technology/ddbs/tidb%E4%BD%BF%E7%94%A8/" selected>简体中文</option></select>
                </a></div>
    </div>
</header>
<div class="search-dropdown desktop">
    <div id="search-dropdown-desktop"></div>
</div>
<div class="search-dropdown mobile">
    <div id="search-dropdown-mobile"></div>
</div>
<main class="main">
                <div class="container"><div class="page single special"><h1 class="single-title animated pulse faster">TIDB使用</h1><div class="content" id="content"><h2 id="tidb-数据库集群">TIDB 数据库集群</h2>
<h3 id="一tidb数据介绍">一、TiDB数据介绍</h3>
<h4 id="11tidb数据简介">1.1、TiDB数据简介</h4>
<p>TiDB 是 PingCAP 公司设计的开源分布式 HTAP (Hybrid Transactional and Analytical Processing) 数据库，结合了传统的 RDBMS 和 NoSQL 的最佳特性。TiDB 兼容 MySQL，支持无限的水平扩展，具备强一致性和高可用性。TiDB 的目标是为 OLTP (Online Transactional Processing) 和 OLAP (Online Analytical Processing) 场景提供一站式的解决方案。</p>
<p>TiDB 具备如下特性：</p>
<ul>
<li>
<p>高度兼容 MySQL</p>
<p><a href="https://www.pingcap.com/docs-cn/sql/mysql-compatibility/" target="_blank" rel="noopener noreffer">大多数情况下</a>，无需修改代码即可从 MySQL 轻松迁移至 TiDB，分库分表后的 MySQL 集群亦可通过 TiDB 工具进行实时迁移。</p>
</li>
<li>
<p>水平弹性扩展</p>
<p>通过简单地增加新节点即可实现 TiDB 的水平扩展，按需扩展吞吐或存储，轻松应对高并发、海量数据场景。</p>
</li>
<li>
<p>分布式事务</p>
<p>TiDB 100% 支持标准的 ACID 事务。</p>
</li>
<li>
<p>真正金融级高可用</p>
<p>相比于传统主从 (M-S) 复制方案，基于 Raft 的多数派选举协议可以提供金融级的 100% 数据强一致性保证，且在不丢失大多数副本的前提下，可以实现故障的自动恢复 (auto-failover)，无需人工介入。</p>
</li>
<li>
<p>一站式 HTAP 解决方案</p>
<p>TiDB 作为典型的 OLTP 行存数据库，同时兼具强大的 OLAP 性能，配合 TiSpark，可提供一站式 HTAP 解决方案，一份存储同时处理 OLTP &amp; OLAP，无需传统繁琐的 ETL 过程。</p>
</li>
<li>
<p>云原生 SQL 数据库</p>
<p>TiDB 是为云而设计的数据库，支持公有云、私有云和混合云，使部署、配置和维护变得十分简单。</p>
</li>
</ul>
<p><strong>TiDB Server</strong></p>
<p>TiDB Server 负责接收 SQL 请求，处理 SQL 相关的逻辑，并通过 PD 找到存储计算所需数据的 TiKV 地址，与 TiKV 交互获取数据，最终返回结果。TiDB Server 是无状态的，其本身并不存储数据，只负责计算，可以无限水平扩展，可以通过负载均衡组件（如LVS、HAProxy 或 F5）对外提供统一的接入地址。</p>
<p><strong>PD Server</strong></p>
<p>Placement Driver (简称 PD) 是整个集群的管理模块，其主要工作有三个：一是存储集群的元信息（某个 Key 存储在哪个 TiKV 节点）；二是对 TiKV 集群进行调度和负载均衡（如数据的迁移、Raft group leader 的迁移等）；三是分配全局唯一且递增的事务 ID。</p>
<p>PD 是一个集群，需要部署奇数个节点，一般线上推荐至少部署 3 个节点</p>
<p><strong>TiKV Server</strong></p>
<p>TiKV Server 负责存储数据，从外部看 TiKV 是一个分布式的提供事务的 Key-Value 存储引擎。存储数据的基本单位是 Region，每个 Region 负责存储一个 Key Range（从 StartKey 到 EndKey 的左闭右开区间）的数据，每个 TiKV 节点会负责多个 Region。TiKV 使用 Raft 协议做复制，保持数据的一致性和容灾。副本以 Region 为单位进行管理，不同节点上的多个 Region 构成一个 Raft Group，互为副本。数据在多个 TiKV 之间的负载均衡由 PD 调度，这里也是以 Region 为单位进行调度</p>
<p><strong>TiSpark</strong></p>
<p>TiSpark 作为 TiDB 中解决用户复杂 OLAP 需求的主要组件，将 Spark SQL 直接运行在 TiDB 存储层上，同时融合 TiKV 分布式集群的优势，并融入大数据社区生态。至此，TiDB 可以通过一套系统，同时支持 OLTP 与 OLAP，免除用户数据同步的烦恼</p>
<h4 id="12tidb-数据基本操作">1.2、Tidb 数据基本操作</h4>
<p><strong>创建、查看和删除数据库</strong></p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-sql" data-lang="sql"><span class="k">CREATE</span> <span class="k">DATABASE</span> <span class="n">db_name</span> <span class="p">[</span><span class="k">options</span><span class="p">];</span>
<span class="k">CREATE</span> <span class="k">DATABASE</span> <span class="k">IF</span> <span class="k">NOT</span> <span class="k">EXISTS</span> <span class="n">samp_db</span><span class="p">;</span>
<span class="k">DROP</span> <span class="k">DATABASE</span> <span class="n">samp_db</span><span class="p">;</span>
<span class="k">DROP</span> <span class="k">TABLE</span> <span class="k">IF</span> <span class="k">EXISTS</span> <span class="n">person</span><span class="p">;</span>
<span class="k">CREATE</span> <span class="k">INDEX</span> <span class="n">person_num</span> <span class="k">ON</span> <span class="n">person</span> <span class="p">(</span><span class="nb">number</span><span class="p">);</span>
<span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">person</span> <span class="k">ADD</span> <span class="k">INDEX</span> <span class="n">person_num</span> <span class="p">(</span><span class="nb">number</span><span class="p">);</span>
<span class="k">CREATE</span> <span class="k">UNIQUE</span> <span class="k">INDEX</span> <span class="n">person_num</span> <span class="k">ON</span> <span class="n">person</span> <span class="p">(</span><span class="nb">number</span><span class="p">);</span>
<span class="k">CREATE</span> <span class="k">USER</span> <span class="s1">&#39;tiuser&#39;</span><span class="o">@</span><span class="s1">&#39;localhost&#39;</span> <span class="n">IDENTIFIED</span> <span class="k">BY</span> <span class="s1">&#39;123456&#39;</span><span class="p">;</span>
<span class="k">GRANT</span> <span class="k">SELECT</span> <span class="k">ON</span> <span class="n">samp_db</span><span class="p">.</span><span class="o">*</span> <span class="k">TO</span> <span class="s1">&#39;tiuser&#39;</span><span class="o">@</span><span class="s1">&#39;localhost&#39;</span><span class="p">;</span>
<span class="k">SHOW</span> <span class="n">GRANTS</span> <span class="k">for</span> <span class="n">tiuser</span><span class="o">@</span><span class="n">localhost</span><span class="p">;</span>
<span class="k">DROP</span> <span class="k">USER</span> <span class="s1">&#39;tiuser&#39;</span><span class="o">@</span><span class="s1">&#39;localhost&#39;</span><span class="p">;</span>
<span class="k">GRANT</span> <span class="k">ALL</span> <span class="k">PRIVILEGES</span> <span class="k">ON</span> <span class="n">test</span><span class="p">.</span><span class="o">*</span> <span class="k">TO</span> <span class="s1">&#39;xxxx&#39;</span><span class="o">@</span><span class="s1">&#39;%&#39;</span> <span class="n">IDENTIFIED</span> <span class="k">BY</span> <span class="s1">&#39;yyyyy&#39;</span><span class="p">;</span>
<span class="k">REVOKE</span> <span class="k">ALL</span> <span class="k">PRIVILEGES</span> <span class="k">ON</span> <span class="o">`</span><span class="n">test</span><span class="o">`</span><span class="p">.</span><span class="o">*</span> <span class="k">FROM</span> <span class="s1">&#39;genius&#39;</span><span class="o">@</span><span class="s1">&#39;localhost&#39;</span><span class="p">;</span>
<span class="k">SHOW</span> <span class="n">GRANTS</span> <span class="k">for</span> <span class="s1">&#39;root&#39;</span><span class="o">@</span><span class="s1">&#39;%&#39;</span><span class="p">;</span>
<span class="k">SELECT</span> <span class="n">Insert_priv</span> <span class="k">FROM</span> <span class="n">mysql</span><span class="p">.</span><span class="k">user</span> <span class="k">WHERE</span> <span class="k">user</span><span class="o">=</span><span class="s1">&#39;test&#39;</span> <span class="k">AND</span> <span class="k">host</span><span class="o">=</span><span class="s1">&#39;%&#39;</span><span class="p">;</span>
<span class="n">FLUSH</span> <span class="k">PRIVILEGES</span><span class="p">;</span>
</code></pre></td></tr></table>
</div>
</div><h3 id="二tidb-ansible-部署">二、TiDB Ansible 部署</h3>
<h4 id="21安装tidb集群基础环境">2.1、安装Tidb集群基础环境</h4>
<p>使用三台物理机搭建Tidb集群，三台机器ip 为 172.16.5.50，172.16.5.51，172.16.5.10，其中172.16.5.51作为中控机。</p>
<p>软件安装如下：</p>
<p>172.16.5.51       TiDB,PD,TiKV</p>
<p>172.16.5.50       TiKV</p>
<p>172.16.5.10       TiKV</p>
<p><strong>安装中控机软件</strong></p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-fallback" data-lang="fallback"># yum -y install epel-release git curl sshpass atop vim htop net-tools 
# yum -y install python-pip
</code></pre></td></tr></table>
</div>
</div><p><strong>在中控机上创建 tidb 用户，并生成 ssh key</strong></p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span><span class="lnt">7
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># 创建tidb用户</span>
useradd -m -d /home/tidb tidb <span class="o">&amp;&amp;</span> passwd tidb
<span class="c1"># 配置tidb用户sudo权限</span>
visudo
tidb <span class="nv">ALL</span><span class="o">=(</span>ALL<span class="o">)</span> NOPASSWD: ALL
<span class="c1"># 使用tidb账户生成 ssh key</span>
su tidb <span class="o">&amp;&amp;</span> ssh-keygen -t rsa -C mikel@tidb
</code></pre></td></tr></table>
</div>
</div><p><strong>在中控机器上下载 TiDB-Ansible</strong></p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># 下载Tidb-Ansible 版本</span>
<span class="nb">cd</span> /home/tidb <span class="o">&amp;&amp;</span> git clone -b release-2.0 https://github.com/pingcap/tidb-ansible.git
<span class="c1"># 安装ansible及依赖</span>
<span class="nb">cd</span> /home/tidb/tidb-ansible/ <span class="o">&amp;&amp;</span> pip install -r ./requirements.txt
</code></pre></td></tr></table>
</div>
</div><p><strong>在中控机上配置部署机器ssh互信及sudo 规则</strong></p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># 配置hosts.ini</span>
su tidb <span class="o">&amp;&amp;</span> <span class="nb">cd</span> /home/tidb/tidb-ansible
vim hosts.ini
<span class="o">[</span>servers<span class="o">]</span>
172.16.5.50
172.16.5.51
172.16.5.52
<span class="o">[</span>all:vars<span class="o">]</span>
<span class="nv">username</span> <span class="o">=</span> tidb
<span class="nv">ntp_server</span> <span class="o">=</span> pool.ntp.org
<span class="c1"># 配置ssh 互信</span>
ansible-playbook -i hosts.ini create_users.yml -u root -k
</code></pre></td></tr></table>
</div>
</div><p><strong>在目标机器上安装ntp服务</strong></p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-fallback" data-lang="fallback"># 中控机器上给目标主机安装ntp服务
cd /home/tidb/tidb-ansible
ansible-playbook -i hosts.ini deploy_ntp.yml -u tidb -b
</code></pre></td></tr></table>
</div>
</div><p><strong>目标机器上调整cpufreq</strong></p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># 查看cpupower 调节模式，目前虚拟机不支持，调节10服务器cpupower</span>
cpupower frequency-info --governors
analyzing CPU 0:
  available cpufreq governors: Not Available
<span class="c1"># 配置cpufreq调节模式</span>
cpupower frequency-set --governor performance
</code></pre></td></tr></table>
</div>
</div><p><strong>目标机器上添加数据盘ext4 文件系统挂载</strong></p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span><span class="lnt">19
</span><span class="lnt">20
</span><span class="lnt">21
</span><span class="lnt">22
</span><span class="lnt">23
</span><span class="lnt">24
</span><span class="lnt">25
</span><span class="lnt">26
</span><span class="lnt">27
</span><span class="lnt">28
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># 创建分区表</span>
parted -s -a optimal /dev/nvme0n1 mklabel gpt -- mkpart primary ext4 <span class="m">1</span> -1
<span class="c1"># 手动创建分区</span>
parted  dev/sdb
mklabel gpt
mkpart primary 0KB 210GB 
<span class="c1"># 格式化分区</span>
mkfs.ext4 /dev/sdb
<span class="c1"># 查看数据盘分区 UUID</span>
<span class="o">[</span>root@tidb-tikv1 ~<span class="o">]</span><span class="c1"># lsblk -f</span>
NAME   FSTYPE LABEL UUID                                 MOUNTPOINT
sda                                                      
├─sda1 xfs          f41c3b1b-125f-407c-81fa-5197367feb39 /boot
├─sda2 xfs          8119193b-c774-467f-a057-98329c66b3b3 /
├─sda3                                                   
└─sda5 xfs          42356bb3-911a-4dc4-b56e-815bafd08db2 /home
sdb    ext4         532697e9-970e-49d4-bdba-df386cac34d2 
<span class="c1"># 分别在三台机器上，编辑 /etc/fstab 文件，添加 nodelalloc 挂载参数</span>
vim /etc/fstab
<span class="nv">UUID</span><span class="o">=</span>8119193b-c774-467f-a057-98329c66b3b3 /                       xfs     defaults        <span class="m">0</span> <span class="m">0</span>
<span class="nv">UUID</span><span class="o">=</span>f41c3b1b-125f-407c-81fa-5197367feb39 /boot                   xfs     defaults        <span class="m">0</span> <span class="m">0</span>
<span class="nv">UUID</span><span class="o">=</span>42356bb3-911a-4dc4-b56e-815bafd08db2 /home                   xfs     defaults        <span class="m">0</span> <span class="m">0</span>
<span class="nv">UUID</span><span class="o">=</span>532697e9-970e-49d4-bdba-df386cac34d2 /data                   ext4    defaults,nodelalloc,noatime   <span class="m">0</span> <span class="m">2</span>
<span class="c1"># 挂载数据盘</span>
mkdir /data
mount -a
mount -t ext4
/dev/sdb on /data <span class="nb">type</span> ext4 <span class="o">(</span>rw,noatime,seclabel,nodelalloc,data<span class="o">=</span>ordered<span class="o">)</span>
</code></pre></td></tr></table>
</div>
</div><p><strong>分配机器资源，编辑inventory.ini 文件</strong></p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span><span class="lnt">19
</span><span class="lnt">20
</span><span class="lnt">21
</span><span class="lnt">22
</span><span class="lnt">23
</span><span class="lnt">24
</span><span class="lnt">25
</span><span class="lnt">26
</span><span class="lnt">27
</span><span class="lnt">28
</span><span class="lnt">29
</span><span class="lnt">30
</span><span class="lnt">31
</span><span class="lnt">32
</span><span class="lnt">33
</span><span class="lnt">34
</span><span class="lnt">35
</span><span class="lnt">36
</span><span class="lnt">37
</span><span class="lnt">38
</span><span class="lnt">39
</span><span class="lnt">40
</span><span class="lnt">41
</span><span class="lnt">42
</span><span class="lnt">43
</span><span class="lnt">44
</span><span class="lnt">45
</span><span class="lnt">46
</span><span class="lnt">47
</span><span class="lnt">48
</span><span class="lnt">49
</span><span class="lnt">50
</span><span class="lnt">51
</span><span class="lnt">52
</span><span class="lnt">53
</span><span class="lnt">54
</span><span class="lnt">55
</span><span class="lnt">56
</span><span class="lnt">57
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># 单机Tikv实例</span>
Name                  HostIP                  Services
tidb-tikv1           172.16.5.50             PD1, TiDB1, TiKV1
tidb-tikv2           172.16.5.51             PD2, TiKV2
tidb-tikv3           172.16.5.52             PD3, TiKV3
<span class="c1"># 编辑inventory.ini 文件</span>
<span class="nb">cd</span> /home/tidb/tidb-ansible
vim inventory.ini
<span class="c1">## TiDB Cluster Part</span>
<span class="o">[</span>tidb_servers<span class="o">]</span>
172.16.5.50
172.16.5.51

<span class="o">[</span>tikv_servers<span class="o">]</span>
172.16.5.50
172.16.5.51
172.16.5.52

<span class="o">[</span>pd_servers<span class="o">]</span>
172.16.5.50
172.16.5.51
172.16.5.52

<span class="c1">## Monitoring Part</span>
<span class="c1"># prometheus and pushgateway servers</span>
<span class="o">[</span>monitoring_servers<span class="o">]</span>
172.16.5.50

<span class="c1"># node_exporter and blackbox_exporter servers</span>
<span class="o">[</span>monitored_servers<span class="o">]</span>
172.16.5.50
172.16.5.51
172.16.5.52

<span class="o">[</span>all:vars<span class="o">]</span>
<span class="c1">#deploy_dir = /home/tidb/deploy</span>
<span class="nv">deploy_dir</span> <span class="o">=</span> /data/deploy
<span class="c1"># 检测ssh互信</span>
<span class="o">[</span>tidb@tidb-tikv1 tidb-ansible<span class="o">]</span>$ ansible -i inventory.ini all -m shell -a <span class="s1">&#39;whoami&#39;</span>
172.16.5.51 <span class="p">|</span> SUCCESS <span class="p">|</span> <span class="nv">rc</span><span class="o">=</span><span class="m">0</span> &gt;&gt;
tidb
172.16.5.52 <span class="p">|</span> SUCCESS <span class="p">|</span> <span class="nv">rc</span><span class="o">=</span><span class="m">0</span> &gt;&gt;
tidb
172.16.5.50 <span class="p">|</span> SUCCESS <span class="p">|</span> <span class="nv">rc</span><span class="o">=</span><span class="m">0</span> &gt;&gt;
tidb
<span class="c1"># 检测tidb 用户 sudo 免密码配置</span>
<span class="o">[</span>tidb@tidb-tikv1 tidb-ansible<span class="o">]</span>$ ansible -i inventory.ini all -m shell -a <span class="s1">&#39;whoami&#39;</span> -b
172.16.5.52 <span class="p">|</span> SUCCESS <span class="p">|</span> <span class="nv">rc</span><span class="o">=</span><span class="m">0</span> &gt;&gt;
root
172.16.5.51 <span class="p">|</span> SUCCESS <span class="p">|</span> <span class="nv">rc</span><span class="o">=</span><span class="m">0</span> &gt;&gt;
root
172.16.5.50 <span class="p">|</span> SUCCESS <span class="p">|</span> <span class="nv">rc</span><span class="o">=</span><span class="m">0</span> &gt;&gt;
root
<span class="c1"># 执行 local_prepare.yml playbook，联网下载 TiDB binary 到中控机</span>
ansible-playbook local_prepare.yml
<span class="c1"># 初始化系统环境，修改内核参数</span>
ansible-playbook bootstrap.yml
</code></pre></td></tr></table>
</div>
</div><h4 id="22安装tidb集群">2.2、安装Tidb集群</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell">ansible-playbook deploy.yml
</code></pre></td></tr></table>
</div>
</div><h4 id="23启动tidb集群">2.3、启动Tidb集群</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-fallback" data-lang="fallback">ansible-playbook start.yml
</code></pre></td></tr></table>
</div>
</div><h4 id="24测试集群">2.4、测试集群</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># 使用 MySQL 客户端连接测试，TCP 4000 端口是 TiDB 服务默认端口</span>
mysql -u root -h 172.16.5.50 -P <span class="m">4000</span>
mysql&gt; show databases<span class="p">;</span>
+--------------------+
<span class="p">|</span> Database           <span class="p">|</span>
+--------------------+
<span class="p">|</span> INFORMATION_SCHEMA <span class="p">|</span>
<span class="p">|</span> PERFORMANCE_SCHEMA <span class="p">|</span>
<span class="p">|</span> mysql              <span class="p">|</span>
<span class="p">|</span> <span class="nb">test</span>               <span class="p">|</span>
+--------------------+
<span class="m">4</span> rows in <span class="nb">set</span> <span class="o">(</span>0.00 sec<span class="o">)</span>
<span class="c1"># 通过浏览器访问监控平台</span>
地址：http://172.16.5.51:3000 默认帐号密码是：admin/admin
</code></pre></td></tr></table>
</div>
</div><h3 id="三tidb集群扩容">三、TIDB集群扩容</h3>
<h4 id="31扩容-tidbtikv-节点">3.1、扩容 TiDB/TiKV 节点</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span><span class="lnt">19
</span><span class="lnt">20
</span><span class="lnt">21
</span><span class="lnt">22
</span><span class="lnt">23
</span><span class="lnt">24
</span><span class="lnt">25
</span><span class="lnt">26
</span><span class="lnt">27
</span><span class="lnt">28
</span><span class="lnt">29
</span><span class="lnt">30
</span><span class="lnt">31
</span><span class="lnt">32
</span><span class="lnt">33
</span><span class="lnt">34
</span><span class="lnt">35
</span><span class="lnt">36
</span><span class="lnt">37
</span><span class="lnt">38
</span><span class="lnt">39
</span><span class="lnt">40
</span><span class="lnt">41
</span><span class="lnt">42
</span><span class="lnt">43
</span><span class="lnt">44
</span><span class="lnt">45
</span><span class="lnt">46
</span><span class="lnt">47
</span><span class="lnt">48
</span><span class="lnt">49
</span><span class="lnt">50
</span><span class="lnt">51
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># 单机Tikv实例</span>
Name                  HostIP                  Services
tidb-tikv1           172.16.5.50             PD1, TiDB1, TiKV1
tidb-tikv2           172.16.5.51             PD2, TiKV2
tidb-tikv3           172.16.5.52             PD3, TiKV3
<span class="c1"># 新增一台TIDB节点</span>
添加一个 TiDB 节点（tidb-tikv4），IP 地址为 172.16.5.53
<span class="c1"># 编辑inventory.ini 文件</span>
<span class="nb">cd</span> /home/tidb/tidb-ansible
vim inventory.ini
<span class="c1">## TiDB Cluster Part</span>
<span class="o">[</span>tidb_servers<span class="o">]</span>
172.16.5.50
172.16.5.51
172.16.5.53

<span class="o">[</span>tikv_servers<span class="o">]</span>
172.16.5.50
172.16.5.51
172.16.5.52

<span class="o">[</span>pd_servers<span class="o">]</span>
172.16.5.50
172.16.5.51
172.16.5.52

<span class="c1">## Monitoring Part</span>
<span class="c1"># prometheus and pushgateway servers</span>
<span class="o">[</span>monitoring_servers<span class="o">]</span>
172.16.5.50

<span class="c1"># node_exporter and blackbox_exporter servers</span>
<span class="o">[</span>monitored_servers<span class="o">]</span>
172.16.5.50
172.16.5.51
172.16.5.52
172.16.5.53
<span class="c1"># 拓扑结构如下</span>
Name                  HostIP                  Services
tidb-tikv1           172.16.5.50             PD1, TiDB1, TiKV1
tidb-tikv2           172.16.5.51             PD2, TiKV2
tidb-tikv3           172.16.5.52             PD3, TiKV3
tidb-tikv4           172.16.5.53             TiDB2
<span class="c1"># 初始化新增节点</span>
ansible-playbook bootstrap.yml -l 172.16.5.53
<span class="c1"># 部署新增节点</span>
ansible-playbook deploy.yml -l 172.16.5.53
<span class="c1"># 启动新节点服务</span>
ansible-playbook start.yml -l 172.16.5.53
<span class="c1"># 更新 Prometheus 配置并重启</span>
ansible-playbook rolling_update_monitor.yml --tags<span class="o">=</span>prometheus
</code></pre></td></tr></table>
</div>
</div><h4 id="32扩容pd节点">3.2、扩容PD节点</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span><span class="lnt">19
</span><span class="lnt">20
</span><span class="lnt">21
</span><span class="lnt">22
</span><span class="lnt">23
</span><span class="lnt">24
</span><span class="lnt">25
</span><span class="lnt">26
</span><span class="lnt">27
</span><span class="lnt">28
</span><span class="lnt">29
</span><span class="lnt">30
</span><span class="lnt">31
</span><span class="lnt">32
</span><span class="lnt">33
</span><span class="lnt">34
</span><span class="lnt">35
</span><span class="lnt">36
</span><span class="lnt">37
</span><span class="lnt">38
</span><span class="lnt">39
</span><span class="lnt">40
</span><span class="lnt">41
</span><span class="lnt">42
</span><span class="lnt">43
</span><span class="lnt">44
</span><span class="lnt">45
</span><span class="lnt">46
</span><span class="lnt">47
</span><span class="lnt">48
</span><span class="lnt">49
</span><span class="lnt">50
</span><span class="lnt">51
</span><span class="lnt">52
</span><span class="lnt">53
</span><span class="lnt">54
</span><span class="lnt">55
</span><span class="lnt">56
</span><span class="lnt">57
</span><span class="lnt">58
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># 拓扑结构如下# 单机Tikv实例</span>
Name                  HostIP                  Services
tidb-tikv1           172.16.5.50             PD1, TiDB1, TiKV1
tidb-tikv2           172.16.5.51             PD2, TiKV2
tidb-tikv3           172.16.5.52             PD3, TiKV3
<span class="c1"># 新增一台PD节点</span>
添加一个 PD 节点（tidb-pd1），IP 地址为 172.16.5.54
<span class="c1"># 编辑inventory.ini 文件</span>
<span class="nb">cd</span> /home/tidb/tidb-ansible
vim inventory.ini
<span class="c1">## TiDB Cluster Part</span>
<span class="o">[</span>tidb_servers<span class="o">]</span>
172.16.5.50
172.16.5.51

<span class="o">[</span>tikv_servers<span class="o">]</span>
172.16.5.50
172.16.5.51
172.16.5.52

<span class="o">[</span>pd_servers<span class="o">]</span>
172.16.5.50
172.16.5.51
172.16.5.52
172.16.5.54

<span class="c1">## Monitoring Part</span>
<span class="c1"># prometheus and pushgateway servers</span>
<span class="o">[</span>monitoring_servers<span class="o">]</span>
172.16.5.50

<span class="c1"># node_exporter and blackbox_exporter servers</span>
<span class="o">[</span>monitored_servers<span class="o">]</span>
172.16.5.50
172.16.5.51
172.16.5.52
172.16.5.54
<span class="c1"># 拓扑结构如下</span>
Name                  HostIP                  Services
tidb-tikv1           172.16.5.50             PD1, TiDB1, TiKV1
tidb-tikv2           172.16.5.51             PD2, TiKV2
tidb-tikv3           172.16.5.52             PD3, TiKV3
tidb-pd1             172.16.5.54             PD4
<span class="c1"># 初始化新增节点</span>
ansible-playbook bootstrap.yml -l 172.16.5.54
<span class="c1"># 部署新增节点</span>
ansible-playbook deploy.yml -l 172.16.5.54
<span class="c1"># 登录新增的 PD 节点，编辑启动脚本：{deploy_dir}/scripts/run_pd.sh</span>
1、移除 --initial-cluster<span class="o">=</span><span class="s2">&#34;xxxx&#34;</span> <span class="se">\ </span>配置。
2、添加 --join<span class="o">=</span><span class="s2">&#34;http://172.16.10.1:2379&#34;</span> <span class="se">\，</span>IP 地址 （172.16.10.1） 可以是集群内现有 PD IP 地址中的任意一个。
3、在新增 PD 节点中手动启动 PD 服务：
<span class="o">{</span>deploy_dir<span class="o">}</span>/scripts/start_pd.sh
4、使用 pd-ctl 检查新节点是否添加成功：
/home/tidb/tidb-ansible/resources/bin/pd-ctl -u <span class="s2">&#34;http://172.16.10.1:2379&#34;</span> -d member
<span class="c1"># 滚动升级整个集群</span>
ansible-playbook rolling_update.yml
<span class="c1"># 更新 Prometheus 配置并重启</span>
ansible-playbook rolling_update_monitor.yml --tags<span class="o">=</span>prometheus
</code></pre></td></tr></table>
</div>
</div><h3 id="四tidb集群测试">四、tidb集群测试</h3>
<h4 id="41sysbench基准库测试">4.1、sysbench基准库测试</h4>
<p><strong>sysbench安装</strong></p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># 二进制安装</span>
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh <span class="p">|</span> sudo bash
sudo yum -y install sysbench
</code></pre></td></tr></table>
</div>
</div><p><strong>性能测试</strong></p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">  1
</span><span class="lnt">  2
</span><span class="lnt">  3
</span><span class="lnt">  4
</span><span class="lnt">  5
</span><span class="lnt">  6
</span><span class="lnt">  7
</span><span class="lnt">  8
</span><span class="lnt">  9
</span><span class="lnt"> 10
</span><span class="lnt"> 11
</span><span class="lnt"> 12
</span><span class="lnt"> 13
</span><span class="lnt"> 14
</span><span class="lnt"> 15
</span><span class="lnt"> 16
</span><span class="lnt"> 17
</span><span class="lnt"> 18
</span><span class="lnt"> 19
</span><span class="lnt"> 20
</span><span class="lnt"> 21
</span><span class="lnt"> 22
</span><span class="lnt"> 23
</span><span class="lnt"> 24
</span><span class="lnt"> 25
</span><span class="lnt"> 26
</span><span class="lnt"> 27
</span><span class="lnt"> 28
</span><span class="lnt"> 29
</span><span class="lnt"> 30
</span><span class="lnt"> 31
</span><span class="lnt"> 32
</span><span class="lnt"> 33
</span><span class="lnt"> 34
</span><span class="lnt"> 35
</span><span class="lnt"> 36
</span><span class="lnt"> 37
</span><span class="lnt"> 38
</span><span class="lnt"> 39
</span><span class="lnt"> 40
</span><span class="lnt"> 41
</span><span class="lnt"> 42
</span><span class="lnt"> 43
</span><span class="lnt"> 44
</span><span class="lnt"> 45
</span><span class="lnt"> 46
</span><span class="lnt"> 47
</span><span class="lnt"> 48
</span><span class="lnt"> 49
</span><span class="lnt"> 50
</span><span class="lnt"> 51
</span><span class="lnt"> 52
</span><span class="lnt"> 53
</span><span class="lnt"> 54
</span><span class="lnt"> 55
</span><span class="lnt"> 56
</span><span class="lnt"> 57
</span><span class="lnt"> 58
</span><span class="lnt"> 59
</span><span class="lnt"> 60
</span><span class="lnt"> 61
</span><span class="lnt"> 62
</span><span class="lnt"> 63
</span><span class="lnt"> 64
</span><span class="lnt"> 65
</span><span class="lnt"> 66
</span><span class="lnt"> 67
</span><span class="lnt"> 68
</span><span class="lnt"> 69
</span><span class="lnt"> 70
</span><span class="lnt"> 71
</span><span class="lnt"> 72
</span><span class="lnt"> 73
</span><span class="lnt"> 74
</span><span class="lnt"> 75
</span><span class="lnt"> 76
</span><span class="lnt"> 77
</span><span class="lnt"> 78
</span><span class="lnt"> 79
</span><span class="lnt"> 80
</span><span class="lnt"> 81
</span><span class="lnt"> 82
</span><span class="lnt"> 83
</span><span class="lnt"> 84
</span><span class="lnt"> 85
</span><span class="lnt"> 86
</span><span class="lnt"> 87
</span><span class="lnt"> 88
</span><span class="lnt"> 89
</span><span class="lnt"> 90
</span><span class="lnt"> 91
</span><span class="lnt"> 92
</span><span class="lnt"> 93
</span><span class="lnt"> 94
</span><span class="lnt"> 95
</span><span class="lnt"> 96
</span><span class="lnt"> 97
</span><span class="lnt"> 98
</span><span class="lnt"> 99
</span><span class="lnt">100
</span><span class="lnt">101
</span><span class="lnt">102
</span><span class="lnt">103
</span><span class="lnt">104
</span><span class="lnt">105
</span><span class="lnt">106
</span><span class="lnt">107
</span><span class="lnt">108
</span><span class="lnt">109
</span><span class="lnt">110
</span><span class="lnt">111
</span><span class="lnt">112
</span><span class="lnt">113
</span><span class="lnt">114
</span><span class="lnt">115
</span><span class="lnt">116
</span><span class="lnt">117
</span><span class="lnt">118
</span><span class="lnt">119
</span><span class="lnt">120
</span><span class="lnt">121
</span><span class="lnt">122
</span><span class="lnt">123
</span><span class="lnt">124
</span><span class="lnt">125
</span><span class="lnt">126
</span><span class="lnt">127
</span><span class="lnt">128
</span><span class="lnt">129
</span><span class="lnt">130
</span><span class="lnt">131
</span><span class="lnt">132
</span><span class="lnt">133
</span><span class="lnt">134
</span><span class="lnt">135
</span><span class="lnt">136
</span><span class="lnt">137
</span><span class="lnt">138
</span><span class="lnt">139
</span><span class="lnt">140
</span><span class="lnt">141
</span><span class="lnt">142
</span><span class="lnt">143
</span><span class="lnt">144
</span><span class="lnt">145
</span><span class="lnt">146
</span><span class="lnt">147
</span><span class="lnt">148
</span><span class="lnt">149
</span><span class="lnt">150
</span><span class="lnt">151
</span><span class="lnt">152
</span><span class="lnt">153
</span><span class="lnt">154
</span><span class="lnt">155
</span><span class="lnt">156
</span><span class="lnt">157
</span><span class="lnt">158
</span><span class="lnt">159
</span><span class="lnt">160
</span><span class="lnt">161
</span><span class="lnt">162
</span><span class="lnt">163
</span><span class="lnt">164
</span><span class="lnt">165
</span><span class="lnt">166
</span><span class="lnt">167
</span><span class="lnt">168
</span><span class="lnt">169
</span><span class="lnt">170
</span><span class="lnt">171
</span><span class="lnt">172
</span><span class="lnt">173
</span><span class="lnt">174
</span><span class="lnt">175
</span><span class="lnt">176
</span><span class="lnt">177
</span><span class="lnt">178
</span><span class="lnt">179
</span><span class="lnt">180
</span><span class="lnt">181
</span><span class="lnt">182
</span><span class="lnt">183
</span><span class="lnt">184
</span><span class="lnt">185
</span><span class="lnt">186
</span><span class="lnt">187
</span><span class="lnt">188
</span><span class="lnt">189
</span><span class="lnt">190
</span><span class="lnt">191
</span><span class="lnt">192
</span><span class="lnt">193
</span><span class="lnt">194
</span><span class="lnt">195
</span><span class="lnt">196
</span><span class="lnt">197
</span><span class="lnt">198
</span><span class="lnt">199
</span><span class="lnt">200
</span><span class="lnt">201
</span><span class="lnt">202
</span><span class="lnt">203
</span><span class="lnt">204
</span><span class="lnt">205
</span><span class="lnt">206
</span><span class="lnt">207
</span><span class="lnt">208
</span><span class="lnt">209
</span><span class="lnt">210
</span><span class="lnt">211
</span><span class="lnt">212
</span><span class="lnt">213
</span><span class="lnt">214
</span><span class="lnt">215
</span><span class="lnt">216
</span><span class="lnt">217
</span><span class="lnt">218
</span><span class="lnt">219
</span><span class="lnt">220
</span><span class="lnt">221
</span><span class="lnt">222
</span><span class="lnt">223
</span><span class="lnt">224
</span><span class="lnt">225
</span><span class="lnt">226
</span><span class="lnt">227
</span><span class="lnt">228
</span><span class="lnt">229
</span><span class="lnt">230
</span><span class="lnt">231
</span><span class="lnt">232
</span><span class="lnt">233
</span><span class="lnt">234
</span><span class="lnt">235
</span><span class="lnt">236
</span><span class="lnt">237
</span><span class="lnt">238
</span><span class="lnt">239
</span><span class="lnt">240
</span><span class="lnt">241
</span><span class="lnt">242
</span><span class="lnt">243
</span><span class="lnt">244
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># cpu性能测试</span>
sysbench --test<span class="o">=</span>cpu --cpu-max-prime<span class="o">=</span><span class="m">20000</span> run
----------------------------------start----------------------------------------
Number of threads: <span class="m">1</span>
Initializing random number generator from current <span class="nb">time</span>
Prime numbers limit: <span class="m">20000</span>
Initializing worker threads...
Threads started!
CPU speed:
    events per second:   286.71
General statistics:
    total time:                          10.0004s
    total number of events:              <span class="m">2868</span>
Latency <span class="o">(</span>ms<span class="o">)</span>:
         min:                                    3.46
         avg:                                    3.49
         max:                                    4.49
         95th percentile:                        3.55
         sum:                                 9997.23
Threads fairness:
    events <span class="o">(</span>avg/stddev<span class="o">)</span>:           2868.0000/0.00
    execution <span class="nb">time</span> <span class="o">(</span>avg/stddev<span class="o">)</span>:   9.9972/0.00
-----------------------------------end-------------------------------------------
<span class="c1"># 线程测试</span>
sysbench --test<span class="o">=</span>threads --num-threads<span class="o">=</span><span class="m">64</span> --thread-yields<span class="o">=</span><span class="m">100</span> --thread-locks<span class="o">=</span><span class="m">2</span> run
------------------------------------start-----------------------------------------
Number of threads: <span class="m">64</span>
Initializing random number generator from current <span class="nb">time</span>
Initializing worker threads...
Threads started!
General statistics:
    total time:                          10.0048s
    total number of events:              <span class="m">108883</span>
Latency <span class="o">(</span>ms<span class="o">)</span>:
         min:                                    0.05
         avg:                                    5.88
         max:                                   49.15
         95th percentile:                       17.32
         sum:                               640073.32
Threads fairness:
    events <span class="o">(</span>avg/stddev<span class="o">)</span>:           1701.2969/36.36
    execution <span class="nb">time</span> <span class="o">(</span>avg/stddev<span class="o">)</span>:   10.0011/0.00
-----------------------------------end-----------------------------------------
<span class="c1"># 磁盘IO测试</span>
sysbench --test<span class="o">=</span>fileio --num-threads<span class="o">=</span><span class="m">16</span> --file-total-size<span class="o">=</span>3G --file-test-mode<span class="o">=</span>rndrw prepare
----------------------------------start-----------------------------------------
<span class="m">128</span> files, 24576Kb each, 3072Mb total
Creating files <span class="k">for</span> the test...
Extra file open flags: <span class="o">(</span>none<span class="o">)</span>
Creating file test_file.0
Creating file test_file.1
Creating file test_file.2
Creating file test_file.3
Creating file test_file.4
Creating file test_file.5
Creating file test_file.6
Creating file test_file.7
Creating file test_file.8
Creating file test_file.9
Creating file test_file.10
Creating file test_file.11
Creating file test_file.12
Creating file test_file.13
Creating file test_file.14
Creating file test_file.15
Creating file test_file.16
Creating file test_file.17
Creating file test_file.18
Creating file test_file.19
Creating file test_file.20
Creating file test_file.21
Creating file test_file.22
Creating file test_file.23
Creating file test_file.24
Creating file test_file.25
Creating file test_file.26
Creating file test_file.27
Creating file test_file.28
Creating file test_file.29
Creating file test_file.30
Creating file test_file.31
Creating file test_file.32
Creating file test_file.33
Creating file test_file.34
Creating file test_file.35
Creating file test_file.36
Creating file test_file.37
Creating file test_file.38
Creating file test_file.39
Creating file test_file.40
Creating file test_file.41
Creating file test_file.42
Creating file test_file.43
Creating file test_file.44
Creating file test_file.45
Creating file test_file.46
Creating file test_file.47
Creating file test_file.48
Creating file test_file.49
Creating file test_file.50
Creating file test_file.51
Creating file test_file.52
Creating file test_file.53
Creating file test_file.54
Creating file test_file.55
Creating file test_file.56
Creating file test_file.57
Creating file test_file.58
Creating file test_file.59
Creating file test_file.60
Creating file test_file.61
Creating file test_file.62
Creating file test_file.63
Creating file test_file.64
Creating file test_file.65
Creating file test_file.66
Creating file test_file.67
Creating file test_file.68
Creating file test_file.69
Creating file test_file.70
Creating file test_file.71
Creating file test_file.72
Creating file test_file.73
Creating file test_file.74
Creating file test_file.75
Creating file test_file.76
Creating file test_file.77
Creating file test_file.78
Creating file test_file.79
Creating file test_file.80
Creating file test_file.81
Creating file test_file.82
Creating file test_file.83
Creating file test_file.84
Creating file test_file.85
Creating file test_file.86
Creating file test_file.87
Creating file test_file.88
Creating file test_file.89
Creating file test_file.90
Creating file test_file.91
Creating file test_file.92
Creating file test_file.93
Creating file test_file.94
Creating file test_file.95
Creating file test_file.96
Creating file test_file.97
Creating file test_file.98
Creating file test_file.99
Creating file test_file.100
Creating file test_file.101
Creating file test_file.102
Creating file test_file.103
Creating file test_file.104
Creating file test_file.105
Creating file test_file.106
Creating file test_file.107
Creating file test_file.108
Creating file test_file.109
Creating file test_file.110
Creating file test_file.111
Creating file test_file.112
Creating file test_file.113
Creating file test_file.114
Creating file test_file.115
Creating file test_file.116
Creating file test_file.117
Creating file test_file.118
Creating file test_file.119
Creating file test_file.120
Creating file test_file.121
Creating file test_file.122
Creating file test_file.123
Creating file test_file.124
Creating file test_file.125
Creating file test_file.126
Creating file test_file.127
<span class="m">3221225472</span> bytes written in 339.76 seconds <span class="o">(</span>9.04 MiB/sec<span class="o">)</span>
----------------------------------end------------------------------------------
sysbench --test<span class="o">=</span>fileio --num-threads<span class="o">=</span><span class="m">16</span> --file-total-size<span class="o">=</span>3G --file-test-mode<span class="o">=</span>rndrw run
----------------------------------start-----------------------------------------
Number of threads: <span class="m">16</span>
Initializing random number generator from current <span class="nb">time</span>
Extra file open flags: <span class="o">(</span>none<span class="o">)</span>
<span class="m">128</span> files, 24MiB each
3GiB total file size
Block size 16KiB
Number of IO requests: <span class="m">0</span>
Read/Write ratio <span class="k">for</span> combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync<span class="o">()</span> each <span class="m">100</span> requests.
Calling fsync<span class="o">()</span> at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w <span class="nb">test</span>
Initializing worker threads...
Threads started!
File operations:
    reads/s:                      299.19
    writes/s:                     199.46
    fsyncs/s:                     816.03
Throughput:
    read, MiB/s:                  4.67
    written, MiB/s:               3.12
General statistics:
    total time:                          10.8270s
    total number of events:              <span class="m">12189</span>
Latency <span class="o">(</span>ms<span class="o">)</span>:
         min:                                    0.00
         avg:                                   13.14
         max:                                  340.58
         95th percentile:                       92.42
         sum:                               160186.15
Threads fairness:
    events <span class="o">(</span>avg/stddev<span class="o">)</span>:           761.8125/216.01
    execution <span class="nb">time</span> <span class="o">(</span>avg/stddev<span class="o">)</span>:   10.0116/0.01
--------------------------------------end---------------------------------------
sysbench --test<span class="o">=</span>fileio --num-threads<span class="o">=</span><span class="m">16</span> --file-total-size<span class="o">=</span>3G --file-test-mode<span class="o">=</span>rndrw cleanup
<span class="c1"># 内存测试</span>
sysbench --test<span class="o">=</span>memory --memory-block-size<span class="o">=</span>8k --memory-total-size<span class="o">=</span>4G run 
------------------------------------start-----------------------------------------
Number of threads: <span class="m">1</span>
Initializing random number generator from current <span class="nb">time</span>
Running memory speed <span class="nb">test</span> with the following options:
  block size: 8KiB
  total size: 4096MiB
  operation: write
  scope: global
Initializing worker threads...
Threads started!
Total operations: <span class="m">524288</span> <span class="o">(</span>1111310.93 per second<span class="o">)</span>
4096.00 MiB transferred <span class="o">(</span>8682.12 MiB/sec<span class="o">)</span>
General statistics:
    total time:                          0.4692s
    total number of events:              <span class="m">524288</span>
Latency <span class="o">(</span>ms<span class="o">)</span>:
         min:                                    0.00
         avg:                                    0.00
         max:                                    0.03
         95th percentile:                        0.00
         sum:                                  381.39

Threads fairness:
    events <span class="o">(</span>avg/stddev<span class="o">)</span>:           524288.0000/0.00
    execution <span class="nb">time</span> <span class="o">(</span>avg/stddev<span class="o">)</span>:   0.3814/0.00
-------------------------------------end---------------------------------------
</code></pre></td></tr></table>
</div>
</div><h4 id="42oltp测试"><strong>4.2、OLTP测试</strong></h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span><span class="lnt">19
</span><span class="lnt">20
</span><span class="lnt">21
</span><span class="lnt">22
</span><span class="lnt">23
</span><span class="lnt">24
</span><span class="lnt">25
</span><span class="lnt">26
</span><span class="lnt">27
</span><span class="lnt">28
</span><span class="lnt">29
</span><span class="lnt">30
</span><span class="lnt">31
</span><span class="lnt">32
</span><span class="lnt">33
</span><span class="lnt">34
</span><span class="lnt">35
</span><span class="lnt">36
</span><span class="lnt">37
</span><span class="lnt">38
</span><span class="lnt">39
</span><span class="lnt">40
</span><span class="lnt">41
</span><span class="lnt">42
</span><span class="lnt">43
</span><span class="lnt">44
</span><span class="lnt">45
</span><span class="lnt">46
</span><span class="lnt">47
</span><span class="lnt">48
</span><span class="lnt">49
</span><span class="lnt">50
</span><span class="lnt">51
</span><span class="lnt">52
</span><span class="lnt">53
</span><span class="lnt">54
</span><span class="lnt">55
</span><span class="lnt">56
</span><span class="lnt">57
</span><span class="lnt">58
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># 登录tidb创建测试数据库</span>
mysql -u root -P <span class="m">4000</span> -h 172.16.5.50
create database sbtest
<span class="c1"># 准备测试数据</span>
sysbench /usr/share/sysbench/oltp_common.lua --mysql-host<span class="o">=</span>172.16.5.50 --mysql-port<span class="o">=</span><span class="m">4000</span> --mysql-user<span class="o">=</span>root --tables<span class="o">=</span><span class="m">20</span> --table_size<span class="o">=</span><span class="m">20000000</span> --threads<span class="o">=</span><span class="m">100</span> --max-requests<span class="o">=</span><span class="m">0</span> prepare
--tables<span class="o">=</span><span class="m">20</span>   <span class="c1"># 创建20个表</span>
--table_size<span class="o">=</span><span class="m">20000000</span>   <span class="c1"># 每个表两千万数据</span>
--threads<span class="o">=</span><span class="m">100</span>           <span class="c1"># 使用100个线程数</span>
---------------------------------报错信息如下------------------------------------------
FATAL: mysql_drv_query<span class="o">()</span> returned error <span class="m">9001</span> <span class="o">(</span>PD server timeout<span class="o">[</span>try again later<span class="o">]</span>
2018/11/23 11:23:19.236 log.go:82: <span class="o">[</span>warning<span class="o">]</span> etcdserver: <span class="o">[</span>timed out waiting <span class="k">for</span> <span class="nb">read</span> index response<span class="o">]</span>
2018/11/23 14:15:17.329 heartbeat_streams.go:97: <span class="o">[</span>error<span class="o">]</span> <span class="o">[</span>store 1<span class="o">]</span> send keepalive message fail: EOF
2018/11/23 14:14:04.603 leader.go:312: <span class="o">[</span>info<span class="o">]</span> leader is deleted
2018/11/23 14:14:04.603 leader.go:103: <span class="o">[</span>info<span class="o">]</span> pd2 is not etcd leader, skip campaign leader and check later
2018/11/23 14:21:10.071 coordinator.go:570: <span class="o">[</span>info<span class="o">]</span> <span class="o">[</span>region 1093<span class="o">]</span> send schedule command: transfer leader from store <span class="m">7</span> to store <span class="m">2</span>
FATAL: mysql_drv_query<span class="o">()</span> returned error <span class="m">1105</span> <span class="o">(</span>Information schema is out of date<span class="o">)</span>
------------------------------------end-----------------------------------------------
<span class="c1"># 调整线程数为10，表数量为10，表数据为2000000 做测试</span>
sysbench /usr/share/sysbench/oltp_common.lua --mysql-host<span class="o">=</span>172.16.5.50 --mysql-port<span class="o">=</span><span class="m">4000</span> --mysql-user<span class="o">=</span>root --tables<span class="o">=</span><span class="m">1</span> --table_size<span class="o">=</span><span class="m">2000000</span> --threads<span class="o">=</span><span class="m">10</span> --max-requests<span class="o">=</span><span class="m">0</span> prepare
--------------------------------------start--------------------------------------------
FATAL: mysql_drv_query<span class="o">()</span> returned error <span class="m">1105</span> <span class="o">(</span>Information schema is out of date<span class="o">)</span> 超时报错
成功写入2张表，其余8张表数据并未写满，写好索引
<span class="c1"># 对tidb集群进行读写测试</span>
sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host<span class="o">=</span>172.16.5.50 --mysql-port<span class="o">=</span><span class="m">4000</span> --mysql-user<span class="o">=</span>root --tables<span class="o">=</span><span class="m">1</span> --table_size<span class="o">=</span><span class="m">2000000</span> --threads<span class="o">=</span><span class="m">10</span> --max-requests<span class="o">=</span><span class="m">0</span> run
----------------------------------------start--------------------------------------
Number of threads: <span class="m">10</span>
Initializing random number generator from current <span class="nb">time</span>
Initializing worker threads...
Threads started!
SQL statistics:
    queries performed:
        read:                            <span class="m">868</span>
        write:                           <span class="m">62</span>
        other:                           <span class="m">310</span>
        total:                           <span class="m">1240</span>
    transactions:                        <span class="m">62</span>     <span class="o">(</span>5.60 per sec.<span class="o">)</span>
    queries:                             <span class="m">1240</span>   <span class="o">(</span>112.10 per sec.<span class="o">)</span>
    ignored errors:                      <span class="m">0</span>      <span class="o">(</span>0.00 per sec.<span class="o">)</span>
    reconnects:                          <span class="m">0</span>      <span class="o">(</span>0.00 per sec.<span class="o">)</span>
General statistics:
    total time:                          11.0594s
    total number of events:              <span class="m">62</span>
Latency <span class="o">(</span>ms<span class="o">)</span>:
         min:                                  944.55
         avg:                                 1757.78
         max:                                 2535.05
         95th percentile:                     2320.55
         sum:                               108982.56
Threads fairness:
    events <span class="o">(</span>avg/stddev<span class="o">)</span>:           6.2000/0.40
    execution <span class="nb">time</span> <span class="o">(</span>avg/stddev<span class="o">)</span>:   10.8983/0.31
------------------------------------start----------------------------------------
<span class="c1"># 使用mysql对比测试</span>
mysql -uroot -P <span class="m">3306</span> -h 172.15.5.154
create database sbtest
sysbench /usr/share/sysbench/oltp_common.lua --mysql-host<span class="o">=</span>172.16.5.154 --mysql-port<span class="o">=</span><span class="m">3306</span> --mysql-user<span class="o">=</span>root --mysql-password<span class="o">=</span>root --tables<span class="o">=</span><span class="m">20</span> --table_size<span class="o">=</span><span class="m">20000000</span> --threads<span class="o">=</span><span class="m">10</span> --max-requests<span class="o">=</span><span class="m">0</span> prepare
使用mysql 做测试未发现报错情况

</code></pre></td></tr></table>
</div>
</div><h4 id="43业务数据测试">4.3、业务数据测试</h4>
<p>sysbench /usr/share/sysbench/oltp_read_write.lua &ndash;mysql-host=172.16.5.50 &ndash;mysql-port=4000 &ndash;mysql-user=root &ndash;tables=20 &ndash;table_size=2000000 &ndash;threads=10 &ndash;max-requests=0 run</p>
</div><div id="comments"></div></div></div>
            </main><footer class="footer">
        <div class="footer-container"><div class="footer-line"><i class="far fa-copyright fa-fw"></i><span itemprop="copyrightYear">2019 - 2021</span><span class="author" itemprop="copyrightHolder">&nbsp;<a href="/" target="_blank">mikel pan</a></span>&nbsp;|&nbsp;<span class="license"><a rel="license external nofollow noopener noreffer" href="https://creativecommons.org/licenses/by-nc/4.0/" target="_blank">CC BY-NC 4.0</a></span><span class="icp-splitter">&nbsp;|&nbsp;</span><br class="icp-br"/>
                    <span class="icp"><a href="https://beian.miit.gov.cn/" target="_blank">粤ICP备2021047442号</a></span></div>
        </div>
    </footer></div>

        <div id="fixed-buttons"><a href="#" id="back-to-top" class="fixed-button" title="回到顶部">
                <i class="fas fa-arrow-up fa-fw"></i>
            </a><a href="#" id="view-comments" class="fixed-button" title="查看评论">
                <i class="fas fa-comment fa-fw"></i>
            </a>
        </div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/lightgallery.js@1.2.0/dist/css/lightgallery.min.css"><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/smooth-scroll@16.1.3/dist/smooth-scroll.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/autocomplete.js@0.37.1/dist/autocomplete.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/algoliasearch@4.2.0/dist/algoliasearch-lite.umd.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/lazysizes@5.2.2/lazysizes.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/lightgallery.js@1.2.0/dist/js/lightgallery.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/lg-thumbnail.js@1.2.0/dist/lg-thumbnail.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/lg-zoom.js@1.2.0/dist/lg-zoom.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/clipboard@2.0.6/dist/clipboard.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/sharer.js@0.4.0/sharer.min.js"></script><script type="text/javascript">window.config={"code":{"copyTitle":"复制到剪贴板","maxShownLines":10},"comment":{},"lightGallery":{"actualSize":false,"exThumbImage":"data-thumbnail","hideBarsDelay":2000,"selector":".lightgallery","speed":400,"thumbContHeight":80,"thumbWidth":80,"thumbnail":true},"search":{"algoliaAppID":"REQJX89W85","algoliaIndex":"index.zh-cn","algoliaSearchKey":"63fa048de9b35627f46672e95abc14df","highlightTag":"em","maxResultLength":10,"noResultsFound":"没有找到结果","snippetLength":50,"type":"algolia"}};</script><script type="text/javascript" src="/js/theme.min.js"></script></body>
</html>
